package com.sinog.biz.interceptor;

import com.sinog.biz.BizApplication;
import com.sinog.biz.util.CodeUrlUtils;
import com.sinog.biz.util.IdGen;
import com.sinog.core.util.DateUtil;
import com.sinog.core.util.JsonUtil;
import com.sinog.core.utilbean.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.Date;

/**
 * @desc 跨省同步/同步到司法部 service接口
 * @author lzq
 * @date -
 */
@Slf4j
@Service
public class SyncData implements SyncInterface {

    /**
     * 同步数据到部级 - 中间库(可直接插入司法部数据库中59.255.21.17:3309)
     * @param sql sql
     * @param template template
     */
    @Override
    public void syncBuSqlBySql(String sql,JdbcTemplate template) {
        //state:1:成功，2：失败
        String state = "2";
        String failurenum = "1";
        String reason = "";
        //type：1：同步到司法部，2：同步到其他省份
        String type = "1";
        try {
            ApplicationContext applicationContext = BizApplication.applicationContext;
            JdbcTemplate bujiJdbcTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
            bujiJdbcTemplate.update(sql);
            state = "1";
            failurenum = "0";
            reason = Constants.HnInterfaceDocking.SUCCESS;
        } catch(CannotGetJdbcConnectionException c) {
            //服务器没开
            log.error("Failed to obtain JDBC Connection",c);
            //需要把数据放到execute_sql_middle表中进行补发
            reason = c.getMessage();
        } catch(BadSqlGrammarException b) {
            //update缺少字段
            log.error("StatementCallback; bad SQL grammar",b);
            //需要把数据放到execute_sql_middle表中进行补发
            reason = b.getMessage();
        } catch(BeansException | DataAccessException e) {
            //其他错误统一在此处理
            log.error("发生异常:",e);
            reason = e.getMessage();
        } finally {
            Date date = new Date();
            String tempSql = "INSERT INTO execute_sql_middle (id,type,state,statement,create_time,update_time,failurenum,reason)" + " VALUES (" + JsonUtil.toText(IdGen.strid()) + "," + JsonUtil.toText(type) + "," + state + "," + JsonUtil.toText(sql) + "," + JsonUtil.toText(DateUtil.returnStringTime(date,"yyyy-MM-dd HH:mm:ss")) + "," + JsonUtil.toText(DateUtil.returnStringTime(date,"yyyy-MM-dd HH:mm:ss")) + "," + JsonUtil.toText(failurenum) + "," + JsonUtil.toText(reason) + ")";
            try {
                template.update(tempSql);
            } catch(DataAccessException e) {
                log.error("发生异常:",e);
            }
        }
    }

    /**
     * 同步数据到省级 - 中间库
     * @param sql sql
     * @param sqlType sqlType
     * @param ksCode 户籍地址所在省code
     * @param prinum prinum
     * @param jdbcTemplate jdbcTemplate
     */
    @Override
    public void syncSjSqlBySql(String sql,String sqlType,String ksCode,String prinum,JdbcTemplate jdbcTemplate) {
        try {
            //本系统省份code
            String localCode = CodeUrlUtils.getLocalCode();
            ApplicationContext applicationContext = BizApplication.applicationContext;
            JdbcTemplate middleJdbcTemplate = (JdbcTemplate)applicationContext.getBean("middleJdbcTemplate");
            String type = "2";
            String state = "2";
            String failurenum = "0";
            String reason = "";
            //该条数据不是本省创建，create_province_code -> local_code ,in_province_code -> prinum.substring(0,2) + "0000"
            if(!prinum.substring(0,Constants.Number.NUM_TWO).equals(localCode.substring(0,Constants.Number.NUM_TWO))) {
                ksCode = prinum.substring(0,2) + "0000";
            }
            //双列管处理
            if(sql.contains("INSERT INTO ab_lieguan")) {
                String placevill = jdbcTemplate.queryForObject("select placevill from ab_prisoners where pri_num = '" + prinum + "'",String.class);
                assert placevill != null;
                ksCode = placevill.substring(0,2) + "0000";
            }
            // 自建省份处理
            if(prinum.contains("Z")) {
                String placevill = jdbcTemplate.queryForObject("select adduserid from prisoner_main where pri_num = '" + prinum + "'",String.class);
                assert placevill != null;
                ksCode = placevill.substring(0,2) + "0000";
            }
            Date date = new Date();
            String tempSql = "INSERT INTO middle (id,pri_num,type,state,statement,create_time,update_time,failurenum,reason,create_province_code,in_province_code)" + " VALUES (" + JsonUtil.toText(IdGen.strid()) + "," + JsonUtil.toText(prinum) + "," + JsonUtil.toText(type) + "," + state + "," + JsonUtil.toText(sql) + "," + JsonUtil.toText(DateUtil.returnStringTime(date,"yyyy-MM-dd HH:mm:ss")) + "," + JsonUtil.toText(DateUtil.returnStringTime(date,"yyyy-MM-dd HH:mm:ss")) + "," + JsonUtil.toText(failurenum) + "," + JsonUtil.toText(reason) + "," + JsonUtil.toText(localCode) + "," + JsonUtil.toText(ksCode) + ")";
            middleJdbcTemplate.update(tempSql);
        } catch(DataAccessException e) {
            log.error("发生异常:",e);
        }
    }

    /**
     * 同步数据对外自建接口中间库
     * @param finalSqlB finalSqlB
     * @param finalTemplate finalTemplate
     * @param priNum priNum
     */
    @Override
    public void syncZjSqlBySql(String finalSqlB,JdbcTemplate finalTemplate,String priNum) {
        //state:1:成功，2：失败
        String state = "2";
        String failurenum = "1";
        String reason = "";
        //type：1：同步到司法部，2：同步到其他省份
        String type = "1";
        try {
            //修改中间库执行状态 -- 完成核查接口 -- 完成衔接接口 状态
            String stateSql = "update state_cq set zj_state = '2' where pri_num='" + priNum + "'";
            String xjSql = "update state_cq set zj_state = '4' where pri_num='" + priNum + "'";
            ApplicationContext applicationContext = BizApplication.applicationContext;
            JdbcTemplate zijianJdbcTemplate = (JdbcTemplate)applicationContext.getBean("zijianJdbcTemplate");
            zijianJdbcTemplate.update(finalSqlB);
            //例如:执行核查操作时UPDATE prisoner_hc SET state='3'时把中间库的zj_state改为2
            if(StringUtils.contains(finalSqlB,"UPDATE prisoner_hc SET state='3'")) {
                zijianJdbcTemplate.update(stateSql);
            }
            ////例如:执行核查操作时UPDATE prisoner_main SET state_xj='1'时把中间库的zj_state改为4
            if(StringUtils.contains(finalSqlB,"UPDATE prisoner_main SET") && StringUtils.contains(finalSqlB,"state_xj='1'")) {
                zijianJdbcTemplate.update(xjSql);
            }
            //log.info("条数:{}",update);tr
            state = "1";
            failurenum = "0";
            reason = Constants.HnInterfaceDocking.SUCCESS;
            // 推送自建数据库test库成功后司法部数据库同样推送一份数据
            JdbcTemplate bujiJdbcTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
            syncBuSqlBySql(finalSqlB,bujiJdbcTemplate);
        } catch(CannotGetJdbcConnectionException c) {
            //服务器没开
            log.error("Failed to obtain JDBC Connection",c);
            //需要把数据放到execute_sql_middle表中进行补发
            reason = c.getMessage();
        } catch(BadSqlGrammarException b) {
            //update缺少字段
            log.error("StatementCallback; bad SQL grammar",b);
            //需要把数据放到execute_sql_middle表中进行补发
            reason = b.getMessage();
        } catch(BeansException | DataAccessException e) {
            //其他错误统一在此处理
            log.error("发生异常:",e);
            reason = e.getMessage();
        } finally {
            Date date = new Date();
            String tempSql = "INSERT INTO execute_sql_middle (id,type,state,statement,create_time,update_time,failurenum,reason)" + " VALUES (" + JsonUtil.toText(IdGen.strid()) + "," + JsonUtil.toText(type) + "," + state + "," + JsonUtil.toText(finalSqlB) + "," + JsonUtil.toText(DateUtil.returnStringTime(date,"yyyy-MM-dd HH:mm:ss")) + "," + JsonUtil.toText(DateUtil.returnStringTime(date,"yyyy-MM-dd HH:mm:ss")) + "," + JsonUtil.toText(failurenum) + "," + JsonUtil.toText(reason) + ")";
            try {
                finalTemplate.update(tempSql);
            } catch(DataAccessException e) {
                log.error("发生异常:",e);
            }
        }
    }
}